-- EXEC SP_CALCULATE_STEP_COST_BYPRODUCT
ALTER PROC SP_CALCULATE_STEP_COST_BYPRODUCT 
AS
BEGIN
	
	DECLARE @LOT_ID			BIGINT
	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @PS_TYPE_NAME	VARCHAR(50) 
	DECLARE @QTY_IN			INT
	DECLARE @GDW			INT
	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR			INT
	DECLARE @WAFER_QTY_IN	INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @NEXT_PROCESS_PS_TYPE VARCHAR(50)
	DECLARE @CUMM_STD_COST	NUMERIC(18,4) 
	DECLARE @SHIP_QTY		INT
	DECLARE @SHIPPEDLOT_ID  INT	

	
	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE

	EXEC SP_UPDATE_COST_FROM_PKG_WAFER_COST_TABLE @YEAR, @QUARTER_NO
		
	DECLARE @LOT_CURSOR CURSOR 
	SET @LOT_CURSOR  = CURSOR FOR 
 	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, WAFER_QTY_IN, QTY_IN, PS_TYPE_NAME FROM LOT_TABLE LT, PROCESS_STEP PS
	WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_CURRENT_STATUS != 'Close'
	
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE_NAME
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SELECT @CUMM_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR

		IF ( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT')
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = ISNULL(@CUMM_STD_COST,0.0), LOT_CUMMULATIVE_COST = ( @WAFER_QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
		END
		ELSE 
		BEGIN
			UPDATE LOT_TABLE SET LOT_COST = ISNULL(@CUMM_STD_COST,0.0), LOT_CUMMULATIVE_COST = ( @QTY_IN * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE LOT_ID = @LOT_ID			
		END

		SET @CUMM_STD_COST	= NULL 
		SET @LOT_ID			= NULL		
		SET @PROCESSSTEP_ID = NULL
		SET @WAFER_QTY_IN	= NULL
		SET @QTY_IN			= NULL
		SET @PS_TYPE_NAME	= NULL

		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @WAFER_QTY_IN, @QTY_IN, @PS_TYPE_NAME
	END	
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR

	-- Since for Shipped lots we need to include the process cost also. This is available as the STD for the next process.
	SET @LOT_CURSOR = CURSOR FOR 
	SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, SHIP_QTY, PS_TYPE_NAME, PS_TYPE, SHIPPEDLOT_ID FROM SHIPPED_LOT SL,LOT_TABLE LT, PROCESS_STEP PS
	WHERE SL.LOT_ID = LT.LOT_ID	AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS != 'CLOSE'

	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @SHIP_QTY, @PS_TYPE_NAME, @PS_TYPE, @SHIPPEDLOT_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN		
		
		SELECT @PROCESSSTEP_ID = NEXT_PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		SELECT @NEXT_PROCESS_PS_TYPE = PS_TYPE FROM Process_Step WHERE ProcessStep_ID = @PROCESSSTEP_ID
		IF @PROCESSSTEP_ID IS NOT NULL
		BEGIN
 			SELECT @CUMM_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR

			IF ( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE = 'Back Grind' )
			BEGIN
				IF @NEXT_PROCESS_PS_TYPE = 'Die Bank'
				BEGIN
					SELECT @GDW = Parameter_Value FROM PROCESS_PARAMETER WHERE ProcessStep_ID = ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'  ) AND Parameter_Key = 'Gross Die/Wafer'
					IF @GDW IS NULL OR @GDW = 0
					BEGIN
						SET @GDW = 1
					END
					SET @CUMM_STD_COST = ISNULL( @CUMM_STD_COST, 0.0) * @GDW
					UPDATE SHIPPED_LOT SET LOT_COST = ISNULL(@CUMM_STD_COST,0.0), LOT_CUMMULATIVE_COST = ( @SHIP_QTY * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				END
				ELSE
				BEGIN
					UPDATE SHIPPED_LOT SET LOT_COST = ISNULL(@CUMM_STD_COST,0.0), LOT_CUMMULATIVE_COST = ( @SHIP_QTY * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				END
			END
			ELSE 
			BEGIN
				UPDATE SHIPPED_LOT SET LOT_COST = ISNULL(@CUMM_STD_COST,0.0), LOT_CUMMULATIVE_COST = ( @SHIP_QTY * ISNULL( @CUMM_STD_COST, 0.0 ) ) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
			END
		END
		SET @CUMM_STD_COST	= NULL 
		SET @LOT_ID			= NULL		
		SET @PROCESSSTEP_ID = NULL
		SET @SHIP_QTY	= NULL
		SET @PS_TYPE_NAME	= NULL
		SET @SHIPPEDLOT_ID = NULL
		FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID, @PROCESSSTEP_ID, @SHIP_QTY, @PS_TYPE_NAME, @PS_TYPE,@SHIPPEDLOT_ID
	END	
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR
END 